package hmis.pos.Repository;

import hmis.pos.Base.BaseRepository.BaseRepository;
import hmis.pos.Entity.RoomPrice;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface RoomPriceRepository extends BaseRepository<RoomPrice> {

    //根据房间类型查询价格
    @Query(value = "select * from roomprice where type = ?1", nativeQuery = true)
    List<RoomPrice> findByType(String type);

    //根据日期查询价格
    @Query(value = "select * from roomprice where to_days(date) = to_days(?1);", nativeQuery = true)
    List<RoomPrice> findByDate(Date date);

    //根据类型和日期查询价格
    @Query(value = "select * from roomprice where type = ?1 and to_days(date) = to_days(?2)", nativeQuery = true)
    List<RoomPrice> findByTypeAndDate(String type,Date date);

    //根据入住时间和退房时间查询价格
    @Query(value = "select * from roomprice where date between ?1 and ?2", nativeQuery = true)
    List<RoomPrice> findByDateTime(Date in_time,Date out_time);

    //根据类型和入住时间和退房时间查询
    //@Query(value="select * from roomprice where type = ?1 and date between date_sub(?2, interval 1 day) and date_sub(?3, interval 1 day)", nativeQuery = true)
    @Query(value="select * from roomprice where type = ?1 and date>=?2 and date<?3", nativeQuery = true)
    List<RoomPrice> findByTypeAndDateBetween(String type,Date in_time,Date out_time);

    //查询今天之后的数据
    @Query(value="select * from roomprice where to_days(date) >= to_days(now()) and type = ?1 ;", nativeQuery = true)
    List<RoomPrice> findAfterToday(String type);

    //查找某一年的数据
    @Query(value="select * from roomprice where year(date)=?1", nativeQuery = true)
    List<RoomPrice> findByYear(int year);

    //查找一年某一月的数据
    @Query(value="select * from roomprice where year(date)=?1 and month(date)=?2", nativeQuery = true)
    List<RoomPrice> findByMonth(int year,int month);
}
